package com.tuanzi.loan.business.repository.statistics.impl;

import com.tuanzi.loan.business.entity.sms.SmsSendTaskStatus;
import com.tuanzi.loan.business.repository.statistics.custom.SmsStatisticsRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepository;
import com.tuanzi.loan.vo.request.statistics.SmsStatisticsRequest;
import com.tuanzi.loan.vo.response.statistics.SmsStatisticsResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.jooq.*;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;

import static com.tuanzi.loan.generated.jooq.loan.tables.LoanSmsCalculateTask.LOAN_SMS_CALCULATE_TASK;
import static com.tuanzi.loan.generated.jooq.loan.tables.LoanSmsSendTask.LOAN_SMS_SEND_TASK;
import static com.tuanzi.loan.generated.jooq.stat.Tables.STAT_COMM_ACTIVITY_REGISTER_NEW;
import static com.tuanzi.loan.generated.jooq.stat.tables.StatLoanShortLink.STAT_LOAN_SHORT_LINK;
import static org.jooq.impl.DSL.date;
import static org.jooq.impl.DSL.sum;

@SuppressWarnings({ "rawtypes" })
@Repository
public class SmsStatisticsRepositoryImpl extends JOOQBaseRepository implements SmsStatisticsRepository {

	@Override
	protected TableImpl getTable() {
		return LOAN_SMS_SEND_TASK;
	}

	private static final DateTimeFormatter FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");

	@Override
	public Page<SmsStatisticsResponse> list(SmsStatisticsRequest searchRequest, Pageable pageable) {
		// SELECT distinct date(execute_time) AS day,short_link FROM loan_sms_send_task WHERE execute_time>= ? and execute_time < ? and short_link = ? and status = 2 ORDER BY day DESC
		Field<java.sql.Date> day = date(LOAN_SMS_SEND_TASK.EXECUTE_TIME).as("day");
		SelectJoinStep<Record2<java.sql.Date, String>> base = dslContext.selectDistinct(day, LOAN_SMS_SEND_TASK.SHORT_LINK).from(LOAN_SMS_SEND_TASK);
		SelectJoinStep<Record2<java.sql.Date, String>> countBase = dslContext.selectDistinct(day, LOAN_SMS_SEND_TASK.SHORT_LINK).from(LOAN_SMS_SEND_TASK);

		StringBuilder sb = new StringBuilder();

		if (searchRequest.getStartDate() != null) {
			sb.append(String.format("AND execute_time>='%s' ", new java.sql.Date(searchRequest.getStartDate().getTime()).toLocalDate().format(FORMATTER)));
		}

		if (searchRequest.getEndDate() != null) {
			sb.append(String.format("AND execute_time<'%s' ", new java.sql.Date(DateUtils.addDays(searchRequest.getEndDate(), 1).getTime()).toLocalDate().format(FORMATTER)));
		}

		if (StringUtils.isNotEmpty(searchRequest.getShortLink())) {
			sb.append(String.format("AND short_link='%s' ", searchRequest.getShortLink()));
		}
		sb.append(String.format("AND status=%s ", SmsSendTaskStatus.SEND_DONE.ordinal()));

		Result<Record2<java.sql.Date, String>> fetch = null;
		fetch = base.where(sb.substring("AND".length())).orderBy(day.desc()).limit(pageable.getPageSize()).offset(pageable.getOffset()).fetch();
		int count = 0;
		count = dslContext.fetchCount(countBase.where(sb.substring("AND".length())));

		return new PageImpl<>(fetch.map((RecordMapper<Record, SmsStatisticsResponse>) record -> {
			SmsStatisticsResponse response = new SmsStatisticsResponse();
			response.setExecuteTime(record.get(day));
			response.setShortLink(record.get(LOAN_SMS_SEND_TASK.SHORT_LINK));

			if (StringUtils.isNotEmpty(response.getShortLink())) {
				response.setClickCount(getUv(response.getShortLink(), response.getExecuteTime()));
			}

			// SELECT id, calculate_task_id, content, platform_channel FROM loan_sms_send_task WHERE execute_time like day% and short_link = ? and status = 2
			List<SmsStatisticsResponse.SendSmsTask> sendTasks = dslContext.select(LOAN_SMS_SEND_TASK.ID, LOAN_SMS_SEND_TASK.CALCULATE_TASK_ID, LOAN_SMS_SEND_TASK.CONTENT, LOAN_SMS_SEND_TASK.PLATFORM_CHANNEL).from(LOAN_SMS_SEND_TASK)
					.where(LOAN_SMS_SEND_TASK.EXECUTE_TIME.like(String.format("%s%%", response.getExecuteTime())).and(LOAN_SMS_SEND_TASK.SHORT_LINK.eq(response.getShortLink())).and(LOAN_SMS_SEND_TASK.STATUS.eq(SmsSendTaskStatus.SEND_DONE.ordinal()))).fetchInto(SmsStatisticsResponse.SendSmsTask.class);

			for (SmsStatisticsResponse.SendSmsTask task : sendTasks) {
				// SELECT name, phone_count from loan_sms_calculate_task WHERE id = ?
				Result<Record2<String, Integer>> result = dslContext.select(LOAN_SMS_CALCULATE_TASK.NAME, LOAN_SMS_CALCULATE_TASK.PHONE_COUNT).from(LOAN_SMS_CALCULATE_TASK).where(LOAN_SMS_CALCULATE_TASK.ID.eq(task.getCalculateTaskId())).fetch();
				for (Record2<String, Integer> taskRecord : result) {
					task.setName(taskRecord.getValue(LOAN_SMS_CALCULATE_TASK.NAME));
					task.setSendCount(taskRecord.getValue(LOAN_SMS_CALCULATE_TASK.PHONE_COUNT));
				}
			}

			response.setSendTasks(sendTasks);

			return response;
		}), pageable, count);
	}

	@Override
	public Integer getUv(String shortLink, Date date) {
		return dslContext.select(sum(STAT_LOAN_SHORT_LINK.UV)).from(STAT_LOAN_SHORT_LINK)
				.where(STAT_LOAN_SHORT_LINK.CDATE.eq(new java.sql.Date(date.getTime()))).and(STAT_LOAN_SHORT_LINK.SHORT_LINK.eq(shortLink)).fetchOneInto(Integer.class);
	}

	@Override
	public SmsStatisticsResponse.ActivityRegisterRecord getRecord(String channel, String activity, Date date) {
		SmsStatisticsResponse.ActivityRegisterRecord arr = new SmsStatisticsResponse.ActivityRegisterRecord();

		Result<Record4<BigDecimal, BigDecimal, BigDecimal, BigDecimal>> result = dslContext
				.select(sum(STAT_COMM_ACTIVITY_REGISTER_NEW.EXTERNAL_REGISTER_COUNT).as("h5RegistrationCount"),sum(STAT_COMM_ACTIVITY_REGISTER_NEW.EXTERNAL_NEW_REGISTER_COUNT).as("h5RegisterCount"), sum(STAT_COMM_ACTIVITY_REGISTER_NEW.LOGIN_COUNT).as("loginCount"), sum(STAT_COMM_ACTIVITY_REGISTER_NEW.APP_ACTIVATE_COUNT_PHONE).as("registerCount")).from(STAT_COMM_ACTIVITY_REGISTER_NEW)
				.where(STAT_COMM_ACTIVITY_REGISTER_NEW.CDATE.eq(new java.sql.Date(date.getTime())).and(STAT_COMM_ACTIVITY_REGISTER_NEW.CHANNEL.eq(channel)).and(STAT_COMM_ACTIVITY_REGISTER_NEW.ACTIVITY_ID.eq(activity))).fetch();

		for (Record4<BigDecimal, BigDecimal, BigDecimal, BigDecimal> record: result) {
			arr.setH5RegistrationCount(record.get("h5RegistrationCount", Integer.class));
			arr.setH5RegisterCount(record.get("h5RegisterCount", Integer.class));
			arr.setLoginCount(record.get("loginCount", Integer.class));
			arr.setRegisterCount(record.get("registerCount", Integer.class));
		}

		return arr;
	}
}
